USE Partitioning;
GO

-- merge partition
ALTER PARTITION SCHEME psOrders 
NEXT USED [SECONDARY];
GO

BEGIN TRANSACTION
	DECLARE @Year DATETIME;
	SET @Year = CAST((SELECT TOP 1 [value] FROM sys.partition_range_values
		WHERE function_id = (SELECT function_id 
			FROM sys.partition_functions
			WHERE name = 'pfOrders')
		ORDER BY boundary_id) AS DATETIME);

	ALTER PARTITION FUNCTION pfOrders() 
	MERGE RANGE (@Year);
COMMIT TRANSACTION

-- for a given partition function, list each of the boundary values
SELECT
	prv.boundary_id,
	prv.value
FROM sys.partition_range_values prv
	INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
WHERE pf.name = 'pfOrders'		-- partition function name
ORDER BY prv.value;

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages,
	p.data_compression_desc
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;

RETURN;

-- add a record with orderdate < 2010-01-01 to the orders table, it automatically goes into the SECONDRY partition
INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
VALUES (1000001, 'C0000000717', 440, 'C', '2009-12-31 00:00:00.000');

-- where did the record go?
SELECT
	p.partition_number,
	fg.name,
	p.rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;

-- we can still modify the schema and add a column
ALTER TABLE dbo.Orders
ADD shipdate DATETIME NULL;

-- enable lock escalation to the partition level on a partitioned table
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = AUTO);

-- backup the filegroup
/*
BACKUP DATABASE Partitioning
	FILEGROUP = N'SECONDARY' 
TO DISK = N'C:\MSSQL\Backup\PartitioningSecondary.bak';
GO
*/

-- Consistency check the database primary filegroup
/*
DBCC CHECKFILEGROUP performs:
DBCC CHECKALLOC of the filegroup
DBCC CHECKTABLE of every table and indexed view in the filegroup
*/
DBCC CHECKFILEGROUP (1) WITH PHYSICAL_ONLY;